package com.example.servlet;

import com.example.util.DatabaseUtil;
import com.fasterxml.jackson.databind.ObjectMapper;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 数据仪表板控制器
 */
@WebServlet("/dashboard")
public class DashboardServlet extends HttpServlet {
    
    private final ObjectMapper objectMapper = new ObjectMapper();
    
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        HttpSession session = request.getSession();
        String username = (String) session.getAttribute("username");
        
        if (username == null) {
            response.sendRedirect("/login");
            return;
        }
        
        try {
            // 获取统计数据
            Map<String, Object> stats = getOrderStatistics();
            
            // 将统计数据转换为JSON字符串
            String statsJson = objectMapper.writeValueAsString(stats);
            request.setAttribute("statsJson", statsJson);
            
            // 获取推荐数据
            Map<String, Object> recommendations = getRecommendations(username);
            
            // 将推荐数据转换为JSON字符串
            String recommendationsJson = objectMapper.writeValueAsString(recommendations);
            request.setAttribute("recommendationsJson", recommendationsJson);
            
            // 转发到仪表板页面
            request.getRequestDispatcher("/WEB-INF/jsp/dashboard.jsp").forward(request, response);
            
        } catch (Exception e) {
            System.err.println("获取仪表板数据失败: " + e.getMessage());
            e.printStackTrace();
            request.setAttribute("error", "数据加载失败");
            request.getRequestDispatcher("/WEB-INF/jsp/error.jsp").forward(request, response);
        }
    }
    
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        String action = request.getParameter("action");
        
        if ("search".equals(action)) {
            handleSearch(request, response);
        } else {
            response.sendError(HttpServletResponse.SC_BAD_REQUEST, "未知的操作");
        }
    }
    
    /**
     * 获取订单统计数据
     */
    private Map<String, Object> getOrderStatistics() throws SQLException {
        Map<String, Object> stats = new HashMap<>();
        
        try (Connection conn = DatabaseUtil.getConnection()) {
            
            // 1. 获取有效/无效订单总数
            stats.put("validInvalidStats", getValidInvalidStats(conn));
            
            // 2. 获取各类别订单数量
            stats.put("categoryStats", getCategoryStats(conn));
            
            // 3. 获取各类别有效/无效统计
            stats.put("categoryValidStats", getCategoryValidStats(conn));
            
            // 4. 获取热门产品TOP3
            stats.put("topProducts", getTopProducts(conn));
        }
        
        return stats;
    }
    
    private List<Map<String, Object>> getValidInvalidStats(Connection conn) throws SQLException {
        List<Map<String, Object>> stats = new ArrayList<>();
        String sql = "SELECT valid_orders, invalid_orders FROM order_valid_stats";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            if (rs.next()) {
                Map<String, Object> validStat = new HashMap<>();
                validStat.put("name", "有效订单");
                validStat.put("value", rs.getLong("valid_orders"));
                stats.add(validStat);
                
                Map<String, Object> invalidStat = new HashMap<>();
                invalidStat.put("name", "无效订单");
                invalidStat.put("value", rs.getLong("invalid_orders"));
                stats.add(invalidStat);
            }
        }
        
        return stats;
    }
    
    private List<Map<String, Object>> getCategoryStats(Connection conn) throws SQLException {
        List<Map<String, Object>> stats = new ArrayList<>();
        String sql = "SELECT category, order_count FROM category_order_stats ORDER BY order_count DESC";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            while (rs.next()) {
                Map<String, Object> stat = new HashMap<>();
                stat.put("name", rs.getString("category"));
                stat.put("value", rs.getLong("order_count"));
                stats.add(stat);
            }
        }
        
        return stats;
    }
    
    private List<Map<String, Object>> getCategoryValidStats(Connection conn) throws SQLException {
        List<Map<String, Object>> stats = new ArrayList<>();
        String sql = "SELECT category, valid_count, invalid_count FROM category_valid_stats ORDER BY category";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            while (rs.next()) {
                String category = rs.getString("category");
                
                Map<String, Object> validStat = new HashMap<>();
                validStat.put("category", category);
                validStat.put("type", "有效");
                validStat.put("value", rs.getLong("valid_count"));
                stats.add(validStat);
                
                Map<String, Object> invalidStat = new HashMap<>();
                invalidStat.put("category", category);
                invalidStat.put("type", "无效");
                invalidStat.put("value", rs.getLong("invalid_count"));
                stats.add(invalidStat);
            }
        }
        
        return stats;
    }
    
    private List<Map<String, Object>> getTopProducts(Connection conn) throws SQLException {
        List<Map<String, Object>> products = new ArrayList<>();
        String sql = "SELECT product, order_count FROM product_order_stats ORDER BY order_count DESC LIMIT 3";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            while (rs.next()) {
                Map<String, Object> product = new HashMap<>();
                product.put("name", rs.getString("product"));
                product.put("count", rs.getLong("order_count"));
                products.add(product);
            }
        }
        
        return products;
    }
    
    /**
     * 获取推荐数据
     */
    private Map<String, Object> getRecommendations(String username) throws SQLException {
        Map<String, Object> recommendations = new HashMap<>();
        
        try (Connection conn = DatabaseUtil.getConnection()) {
            
            // 获取基于用户的推荐（前3个）
            List<Map<String, Object>> userBasedRecs = getUserBasedRecommendations(conn, username, 3);
            recommendations.put("userBased", userBasedRecs);
            
            // 获取基于物品的推荐（前3个）
            List<Map<String, Object>> itemBasedRecs = getItemBasedRecommendations(conn, 3);
            recommendations.put("itemBased", itemBasedRecs);
            
            // 获取离线推荐（热门类别前3）
            List<Map<String, Object>> offlineRecs = getOfflineRecommendations(conn, 3);
            recommendations.put("offline", offlineRecs);
        }
        
        return recommendations;
    }
    
    private List<Map<String, Object>> getUserBasedRecommendations(Connection conn, String username, int limit) 
            throws SQLException {
        List<Map<String, Object>> recs = new ArrayList<>();
        
        // 先尝试查询具体用户的推荐，如果没有则返回默认推荐
        String sql = "SELECT recommendations FROM user_recommendations WHERE user_id = ? LIMIT 1";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, username);
            
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    String json = rs.getString("recommendations");
                    recs = parseRecommendationJson(json, limit);
                }
            }
        }
        
        // 如果当前用户没有推荐数据，尝试获取任意用户的推荐数据作为示例
        if (recs.isEmpty()) {
            sql = "SELECT recommendations FROM user_recommendations LIMIT 1";
            try (PreparedStatement stmt = conn.prepareStatement(sql);
                 ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    String json = rs.getString("recommendations");
                    recs = parseRecommendationJson(json, limit);
                }
            }
        }
        
        // 如果还是没有推荐数据，返回默认推荐
        if (recs.isEmpty()) {
            recs = getDefaultRecommendations(conn, limit);
        }
        
        return recs;
    }
    
    private List<Map<String, Object>> getItemBasedRecommendations(Connection conn, int limit) 
            throws SQLException {
        List<Map<String, Object>> recs = new ArrayList<>();
        String sql = "SELECT product, recommendations FROM item_recommendations LIMIT ?";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, limit);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> rec = new HashMap<>();
                    rec.put("product", rs.getString("product"));
                    rec.put("recommendations", rs.getString("recommendations"));
                    recs.add(rec);
                }
            }
        }
        
        // 如果没有推荐数据，返回默认推荐
        if (recs.isEmpty()) {
            recs = getDefaultRecommendations(conn, limit);
        }
        
        return recs;
    }
    
    private List<Map<String, Object>> getOfflineRecommendations(Connection conn, int limit) 
            throws SQLException {
        List<Map<String, Object>> recs = new ArrayList<>();
        String sql = "SELECT category, order_count FROM category_order_stats ORDER BY order_count DESC LIMIT ?";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, limit);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> rec = new HashMap<>();
                    rec.put("name", rs.getString("category"));
                    rec.put("count", rs.getLong("order_count"));
                    rec.put("type", "热门类别");
                    recs.add(rec);
                }
            }
        }
        
        return recs;
    }
    
    private List<Map<String, Object>> getDefaultRecommendations(Connection conn, int limit) 
            throws SQLException {
        List<Map<String, Object>> recs = new ArrayList<>();
        String sql = "SELECT product, order_count FROM product_order_stats ORDER BY order_count DESC LIMIT ?";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, limit);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> rec = new HashMap<>();
                    rec.put("name", rs.getString("product"));
                    rec.put("count", rs.getLong("order_count"));
                    rec.put("type", "热门产品");
                    recs.add(rec);
                }
            }
        }
        
        return recs;
    }
    
    private List<Map<String, Object>> parseRecommendationJson(String json, int limit) {
        List<Map<String, Object>> recs = new ArrayList<>();
        
        try {
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> items = objectMapper.readValue(json, List.class);
            
            return items.stream()
                    .limit(limit)
                    .collect(ArrayList::new, (list, item) -> list.add(item), ArrayList::addAll);
                    
        } catch (Exception e) {
            System.err.println("解析推荐JSON失败: " + e.getMessage());
            return recs;
        }
    }
    
    private void handleSearch(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        String searchTerm = request.getParameter("searchTerm");
        if (searchTerm == null || searchTerm.trim().isEmpty()) {
            response.sendError(HttpServletResponse.SC_BAD_REQUEST, "搜索关键词不能为空");
            return;
        }
        
        try {
            Map<String, Object> searchResult = performCategorySearch(searchTerm.trim());
            
            // 设置响应类型为JSON
            response.setContentType("application/json;charset=UTF-8");
            
            // 将搜索结果转换为JSON并返回
            String jsonResult = objectMapper.writeValueAsString(searchResult);
            response.getWriter().write(jsonResult);
            
        } catch (Exception e) {
            System.err.println("搜索处理失败: " + e.getMessage());
            e.printStackTrace();
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "搜索失败");
        }
    }
    
    private Map<String, Object> performCategorySearch(String searchTerm) throws SQLException {
        Map<String, Object> result = new HashMap<>();
        
        try (Connection conn = DatabaseUtil.getConnection()) {
            
            // 搜索匹配的类别
            Map<String, Object> matchedCategory = searchMatchedCategory(conn, searchTerm);
            result.put("matched", matchedCategory);
            
            // 获取相似类别推荐
            List<Map<String, Object>> similarCategories = getSimilarCategories(conn, searchTerm, 3);
            result.put("similar", similarCategories);
            
            // 获取该类别的热门产品
            List<Map<String, Object>> categoryProducts = getCategoryProducts(conn, searchTerm, 3);
            result.put("products", categoryProducts);
        }
        
        return result;
    }
    
    private Map<String, Object> searchMatchedCategory(Connection conn, String searchTerm) throws SQLException {
        Map<String, Object> category = new HashMap<>();
        
        // 模糊搜索类别
        String sql = "SELECT category, " +
                    "SUM(CASE WHEN is_valid = 'Y' THEN 1 ELSE 0 END) as valid_count, " +
                    "SUM(CASE WHEN is_valid = 'N' THEN 1 ELSE 0 END) as invalid_count, " +
                    "COUNT(*) as total_count " +
                    "FROM orders WHERE category LIKE ? GROUP BY category LIMIT 1";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%" + searchTerm + "%");
            
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    category.put("name", rs.getString("category"));
                    category.put("validCount", rs.getLong("valid_count"));
                    category.put("invalidCount", rs.getLong("invalid_count"));
                    category.put("totalCount", rs.getLong("total_count"));
                    category.put("found", true);
                } else {
                    category.put("name", searchTerm);
                    category.put("validCount", 0);
                    category.put("invalidCount", 0);
                    category.put("totalCount", 0);
                    category.put("found", false);
                }
            }
        }
        
        return category;
    }
    
    private List<Map<String, Object>> getSimilarCategories(Connection conn, String searchTerm, int limit) 
            throws SQLException {
        List<Map<String, Object>> categories = new ArrayList<>();
        
        // 获取其他类别，按订单数量排序
        String sql = "SELECT category, order_count FROM category_order_stats " +
                    "WHERE category != ? AND category NOT LIKE ? " +
                    "ORDER BY order_count DESC LIMIT ?";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, searchTerm);
            stmt.setString(2, "%" + searchTerm + "%");
            stmt.setInt(3, limit);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> category = new HashMap<>();
                    category.put("name", rs.getString("category"));
                    category.put("count", rs.getLong("order_count"));
                    // 模拟相似度计算
                    category.put("similarity", String.format("%.2f", 0.9 - Math.random() * 0.3));
                    categories.add(category);
                }
            }
        }
        
        return categories;
    }
    
    private List<Map<String, Object>> getCategoryProducts(Connection conn, String searchTerm, int limit) 
            throws SQLException {
        List<Map<String, Object>> products = new ArrayList<>();
        
        // 获取该类别下的热门产品
        String sql = "SELECT product, COUNT(*) as product_count " +
                    "FROM orders WHERE category LIKE ? " +
                    "GROUP BY product ORDER BY product_count DESC LIMIT ?";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%" + searchTerm + "%");
            stmt.setInt(2, limit);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> product = new HashMap<>();
                    product.put("name", rs.getString("product"));
                    product.put("count", rs.getLong("product_count"));
                    products.add(product);
                }
            }
        }
        
        return products;
    }
} 